package com.zyg.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zyg.pojo.Student;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    @Select("SELECT g.grade_name, COUNT(*) as count "+
            "FROM student_info s "+
            "JOIN sys_class c ON s.class_id = c.class_id " +
            "JOIN sys_grade g ON c.grade_id = g.grade_id  " +
            "GROUP BY g.grade_name "+
            "ORDER BY g.grade_name ASC;")
    List<Map<String, Object>> getGradeDistribution();


    @Select("SELECT major, COUNT(*) as count FROM student_info GROUP BY major")
    List<Map<String, Object>> getMajorDistribution();

    @Select("SELECT hotel_id AS hotelId, COUNT(*) AS count FROM student_info WHERE del_flag = '0' GROUP BY hotel_id")
    List<Map<String, Object>> getHotelDistribution();

    @Select("SELECT age, COUNT(*) as count FROM student_info GROUP BY age")
    List<Map<String, Object>> getAgeDistribution();

}